-- Will check an access key, and delete all related access items if it's a local custom
-- access key, or do nothing, if it's a shared 'area' access key
CREATE PROCEDURE [dbo].[asi_AccessKeyDelete]
    @accessKey uniqueidentifier
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @scope nvarchar(20)
    SELECT @scope = [AccessScope] FROM [dbo].[AccessMain] WHERE [AccessKey] = @accessKey
    IF (@scope = 'Local')
    BEGIN
        DELETE FROM [dbo].[AccessItem] WHERE [AccessKey] = @accessKey
        DELETE FROM [dbo].[AccessMain] WHERE [AccessKey] = @accessKey
    END
    SET NOCOUNT OFF
END

GO